财务:期初本金公式怎么写?
前两天接了一个Excel的定制,给钱的那种。
问题不复杂,半个多小时就搞定了。但是整个沟通过程花了我近4个小时。
这也是我不太愿意接Excel定制的原因。
会Excel的人,不懂业务流程。懂业务流程的人,不会Excel。
Excel的定制很花时间,不是花在做表格上,而是花在沟通上。
所以,如果不是每天闲的难受,我劝你别做Excel定制。
1- 问题描述
回到文章的主题。
今天收到一个财务同学的提问:「期初本金」的公式太长了,怎么能改的简单一点?
图片放大一点看
完整的公式是这样的:
=IF(Z3=2,Y3+AE3,IF(Z3=3,Y3+AE3+AG3,IF(Z3=4,Y3+AE3+AG3+AI3,IF(Z3=5,Y3+AE3+AG3+AI3+AK3,IF(Z3=6,Y3+AE3+AG3+AI3+AK3+AM3,IF(Z3=7,Y3+AE3+AG3+AI3+AK3+AM3+AO3,IF(Z3=8,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3,IF(Z3=9,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3,IF(Z3=10,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3+AU3,IF(Z3=11,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3+AU3+AW3,IF(Z3=1,Y3)))))))))))
做完定制刚平复下来的心情,再次掀起波澜。
2- 原因分析
解决问题没有头绪的时候,使用框架,按照步骤一步一步去分析,会更快一些。
按照2W1H的框架套一下,思路梳理如下。
WHAT,什么是期初本金
什么是「期初本金」?
直接根据公式可以大概猜出「期初本金」的计算规则。
1- 确认已还款期数
确认Z列中的「已还款期数」。
2- 计算期数对应的本金之和
假如期数是3,那么就把这个期数之前的「本金」1和2都加起来,最后和Y列的本金相加。
相加的结果,就是「期初本金」
直接百度出来的结果,在没有清楚的解释「期初本金」之前,又丢出了一堆的财务术语。
WHY,为什么公式那么长
因为不同的期数,求和的区域不一样,所以要使用IF函数:
1- 对每个期初进行判断
2- 根据期数不同,求和区域也不同
所以IF公式的判断条件,就变成了这样:
其中Z3代表的是「期数」,把这些条件合并起来,就是开始我们看到的公式:
=IF(Z3=2,Y3+AE3,IF(Z3=3,Y3+AE3+AG3,IF(Z3=4,Y3+AE3+AG3+AI3,IF(Z3=5,Y3+AE3+AG3+AI3+AK3,IF(Z3=6,Y3+AE3+AG3+AI3+AK3+AM3,IF(Z3=7,Y3+AE3+AG3+AI3+AK3+AM3+AO3,IF(Z3=8,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3,IF(Z3=9,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3,IF(Z3=10,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3+AU3,IF(Z3=11,Y3+AE3+AG3+AI3+AK3+AM3+AO3+AQ3+AS3+AU3+AW3,IF(Z3=1,Y3)))))))))))
HOW,怎么把公式改短些
这个问题的本质,就是根据「期数」不同,对不同区域进行求和。
所以这里涉及到两个问题:
1- 根据条件进行求和,多条件求和。
2- 根据条件更新求和区域,动态区域。
具体请看第3个部分。
3- 解决方法
明确了问题的需求,接下来检索一下对应的函数公式。
1- 只对标题中的「本金」进行计算,这是条件求和,用SUMIF函数
2- 选取根据期数,选择求和的范围不同,这是一个动态的区域,用OFFSET函数。
结合这两个需求,重新编写后的公式如下:
=Y3+SUMIF(OFFSET($AD$2,0,0,1,MATCH(Z3,$AD$1:$BX$1,0)-2),$N$2,OFFSET(AD3,0,0,1,MATCH(Z3,$AD$1:$BX$1,0)-2))
简单的拆解一下公式
(点击查看大图)
1- Y3代表的是本金
2- SUMIF函数,根据「期数」不同,对「期数」内的本金进行求和
3- OFFSET函数,根据「期数」动态计算求和区域。
4- 总结
总结一下这个案例。有几点值得借鉴:
1- 梳理需求
根据IF函数梳理清楚需求,把每个条件梳理到表格或思维导图中。
2- 转换思路
把需求转换成函数公式的功能,条件判断?数据查询?条件求和?等等
3- 匹配函数
根据公式需求,去匹配对应的函数,这一步需要有扎实的函数积累。
4- 编写公式
知道了用哪些函数,知道了需求的逻辑,接下来动手编写公式即可。
5- 提问
讲到这里了,大家说一说,这位财务同学,为什么会被这么长的IF函数给困住?
我是拉小登,一个会设计表格的Excel老师